<?php
namespace ltcms\db;

/*
	[LTCMS  System] Copyright (c) 2023-2024 www.ltcms.com
	This is NOT a freeware, use is subject to license.txt
*/

use ltcms\utils\Str;

class Db_mysqli implements Db_contract
{
    protected $links;
    protected $connect = LTCMS_DATABASE_CONNECT?LTCMS_DATABASE_CONNECT:"default";
    protected $config = array();
    protected $ttl = 0;

    /**
     * SELECT - 分页 SQL表达式
     * @var string
     */
    protected $paginateSql = 'SELECT %FIELD% FROM %TABLE%  %JOIN%  %WHERE% %GROUP% %ORDER% %LIMIT% %LOCK% %COMMENT%';

    /**
     * SELECT - 分页-二次查询 SQL表达式
     * @var string
     */
    protected $paginate2Sql = 'SELECT %FIELD% FROM %TABLE%  INNER JOIN ( SELECT id FROM %TABLE% %WHERE% %GROUP% %ORDER% %LIMIT% %LOCK%) as t USING(id) %ORDER% %COMMENT%';

    /**
     * SELECT SQL表达式
     * @var string
     */
    protected $selectSql = 'SELECT %FIELD% FROM %TABLE%  %JOIN%  %WHERE% %GROUP% %ORDER% %LIMIT% %LOCK% %COMMENT%';

    /**
     * find SQL表达式
     * @var string
     */
    protected $findSql = 'SELECT %FIELD% FROM %TABLE% %JOIN%  %WHERE%  %ORDER% Limit 0,1 %LOCK% %COMMENT%';

    /**
     * INSERT SQL表达式
     * @var string
     */
    protected $insertSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';

    /**
     * INSERT ALL SQL表达式
     * @var string
     */
    protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) %DATA% %COMMENT%';

    /**
     * UPDATE SQL表达式
     * @var string
     */
    protected $updateSql = 'UPDATE %TABLE% SET %SET% %WHERE% %COMMENT%';

    /**
     * DELETE SQL表达式
     * @var string
     */
    protected $deleteSql = 'DELETE FROM %TABLE% %WHERE% %COMMENT%';

    /**
     * 条件
     * @var array
     */
    protected $wheres=array();

    /**
     * 条件-额外
     * @var array
     */
    protected $wheres_extra=array();

    /**
     * 排序
     * @var array
     */
    protected $order=array();

    /**
     * 分组
     * @var array
     */
    protected $group=array();

    /**
     * 数据
     * @var array
     */
    protected $data=array();

    /**
     * 数据库表名
     * @var array
     */
    protected $tableName="";

    /**
     * 数据库连表 JOIN
     * @var array
     */
    protected $join=[]; //单成员 JOIN orders ON users.id = orders.user_id

    /**
     * 默认字段名
     * @var array
     */
    protected $fields="*";

    /**
     * 默认条数
     * @var array
     */
    protected $limit="";

    /**
     * 默认注释字段
     * @var array
     */
    protected $comments="";

    /**
     * 默认关闭排它锁
     * @var array
     */
    protected $lock=false;

    /**
     * 是否开启预处理
     * @var array
     */
    protected $prepare=true;

    /**
     * 是否获取sql
     * @var array
     */
    protected $fetchSql=false;

    /**
     * 绑定预处理数据值
     * @var array
     */
    protected $prepareDataValue=array();

    /**
     * 绑定预处理数据字段
     * @var array
     */
    protected $prepareDataField=array();

    /**
     * 绑定预处理数据字段
     * @var array
     */
    protected $prepareReplaceField=array();

    /**
     * 执行sql
     * @var array
     */
    protected $execSql="";

    /**
     * 设置配置
     */
    public function setConfig(){
        $this->config=config("database.{$this->connect}");
    }

    /**
     * 获取配置信息
     * @return array
     */
    public  function getConfig(){
        return $this->config;
    }

    /**
     * 设置原数据
     * @param $content
     * @return \ltcms\db\Db_raw
     */
    static public function raw($content){
        $Db_raw=new Db_raw();
        $Db_raw->setContent($content);
        return $Db_raw;
    }

    /**
     *  链接数据库
     */
    public function connect($config=array()){
        if(!isset($this->links[$this->connect]) || !$this->links[$this->connect]->ping()){
            try{
                if($config){
                    //检测标识下优美与已存在的link,不存在则创建
                    $mysqli = @new \mysqli($config["db_host"], $config["db_user"], $config["db_pwd"], "", $config["db_port"]);
                    $mysqli->set_charset($config["db_charset"]);
                    $ok=$mysqli->select_db($config["db_name"]);
                    if(!$ok){
                        throw new \RuntimeException('database can\'t use: ' . $config["db_name"]);
                    }
                    $mysqli->query("SET character_set_connection={$config["db_charset"]}");
                    $mysqli->query("SET character_set_results={$config["db_charset"]}");
                    $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE,1);
                    $this->links[$this->connect]=$mysqli;
                }else{
                    //检测标识下优美与已存在的link,不存在则创建
                    $mysqli = @new \mysqli($this->config["db_host"], $this->config["db_user"], $this->config["db_pwd"], "", $this->config["db_port"]);
                    $mysqli->set_charset($this->config["db_charset"]);
                    $ok=$mysqli->select_db($this->config["db_name"]);
                    if(!$ok){
                        throw new \RuntimeException('database can\'t use: ' . $this->config["db_name"]);
                    }
                    $mysqli->query("SET character_set_connection={$this->config["db_charset"]}");
                    $mysqli->query("SET character_set_results={$this->config["db_charset"]}");
                    $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE,1);
                    $this->links[$this->connect]=$mysqli;
                }
            }catch (\Throwable $e){
                throw new \RuntimeException('mysqli connection error: ' . $e->getMessage(),500);
            }
        }
        return $this;
    }

    /**
     * 清除拼接参数
     */
    public function clear(){
        $this->prepare=true;
        $this->fetchSql=false;
        $this->prepareReplaceField=array();
        $this->prepareDataField=array();
        $this->prepareDataValue=array();
        $this->order=array();
        $this->group=array();
        $this->wheres=array();
        $this->data=array();
        $this->tableName="";
        $this->comments="";
        $this->limit="";
        $this->fields="*";
        $this->join=array();
        $this->lock=false;
    }

    /**
     * 切换数据库
     */
    public function changeConnect($connect){
        $this->connect=$connect;
        $this->setConfig();
        return $this;
    }

    /**
     * 选择数据库
     * @param $sql
     */
    public function selectDb($dbName){
        $this->connect();
        $this->links[$this->connect]->select_db($dbName);
        return $this;
    }

    /**
     * 开启事务
     */
    public function startTrans(){
        $this->connect();
        $this->links[$this->connect]->begin_transaction();
    }

    /**
     * 提交事务
     */
    public function commitTrans(){
        $this->connect();
        $this->links[$this->connect]->commit();
    }

    /**
     * 回滚事务
     */
    public function rollbackTrans(){
        $this->connect();
        $this->links[$this->connect]->rollback();
    }

    /**
     * where
     */
    public function where($condition=array(),$operate=null,$value=null){
        if($condition){
            if(is_string($condition) && $operate!==null){
                if($value===NULL){
                    $cons=array();
                    $cons[]=array(
                        $condition,"=",$operate
                    );
                    $this->wheres=array_merge($this->wheres,$cons);
                }else{
                    $cons=array();
                    $cons[]=array(
                        $condition,$operate,$value
                    );
                    $this->wheres=array_merge($this->wheres,$cons);
                }
            }else if(is_array($condition)){
                $this->wheres=array_merge($this->wheres,$condition);
            }
        }
        return $this;
    }

    /**
     * wheres_extra
     */
    public function wheres_extra($condition=array(),$operate=null,$value=null){
        if($condition){
            if(is_string($condition) && $operate!==null){
                if($value===NULL){
                    $cons=array();
                    $cons[]=array(
                        $condition,"=",$operate
                    );
                    $this->wheres_extra=array_merge($this->wheres_extra,$cons);
                }else{
                    $cons=array();
                    $cons[]=array(
                        $condition,$operate,$value
                    );
                    $this->wheres_extra=array_merge($this->wheres_extra,$cons);
                }
            }else if(is_array($condition)){
                $this->wheres_extra=array_merge($this->wheres_extra,$condition);
            }
        }
        return $this;
    }

    /**
     * order
     */
    public function order($key,$val=null){
        if(is_array($key)){
            $this->order=array_merge($this->order,$key);
        }else{
            $this->order[$key]=$val;
        }
        return $this;
    }

    /**
     * 设置数据
     */
    public function data($data){
        $this->data=$data;
        return $this;
    }

    /**
     * 设置条数
     */
    public function limit($offset=null,$limit=null){
        if(isset($offset)){
            if(is_null($limit)){
                $this->limit=array(
                    "offset"=>0,
                    "limit"=>$offset,
                );
            }else{
                $this->limit=array(
                    "offset"=>$offset,
                    "limit"=>$limit,
                );
            }
        }
        return $this;
    }

    /**
     * 设置分页
     */
    public function page($page,$pageSize){
        $page=$page?:1;
        $this->limit=array(
            "offset"=>($page - 1) * $pageSize,
            "limit"=>$pageSize,
        );
        return $this;
    }

    /**
     * 绑定预处理数据
     */
    public function bindValue($prepareDataName,$prepareDataValue=null){
        if(is_null($prepareDataValue)){
            if(is_array($prepareDataName)){
                foreach($prepareDataName as $key=>$val){
                    $type=Str::getType($val,2);
                    if($type!==false){
                        $this->prepareDataField[]=$type;
                        $this->prepareDataValue[]=$val;
                        if(!is_numeric($key)){
                            $this->prepareReplaceField[]=":".$key;
                        }
                    }
                }
            }else{
                $type=Str::getType($prepareDataName,2);
                if($type!==false){
                    $this->prepareDataField[]=$type;
                    $this->prepareDataValue[]=$prepareDataName;
                }
            }
        }else{
            $type=Str::getType($prepareDataValue,2);
            if($type!==false){
                $this->prepareDataField[]=$type;
                $this->prepareDataValue[]=$prepareDataValue;
                $this->prepareReplaceField[]=":".$prepareDataName;
            }
        }
        return $this;
    }

    /**
     * 是否加排它锁
     */
    public function lock($lock){
        $this->lock=(bool)$lock;
        return $this;
    }

    /**
     * 是否返回sql
     */
    public function fetchSql($boolean){
        $this->fetchSql=(bool)$boolean;
        return $this;
    }

    /**
     * 是否开启预处理
     */
    public function prepare($prepare){
        $this->prepare=(bool)$prepare;
        return $this;
    }

    /**
     * 当做字符串打印时
     * @return string
     */
    public function __toString()
    {
        return  "";
    }

    /**
     * 避免打印所有数据
     * @return array
     */
    public function __debuginfo(){
        return array();
    }

    /**
     * 设置表名
     */
    public function name($table){
        $this->tableName=$table;
        return $this;
    }

    /**
     * 设置左连接
     */
    public function joinLeft($table,$on){
        $this->join[]="LEFT JOIN $table ON $on"; //加入链表数组
        return $this;
    }

    /**
     * 设置右连接
     */
    public function joinRight($table,$on){
        $this->join[]="RIGHT JOIN $table ON $on"; //加入链表数组
        return $this;
    }

    /**
     * 设置内连接
     */
    public function join($table,$on){
        $this->join[]="JOIN $table ON $on"; //加入链表数组
        return $this;
    }

    /**
     * 设置sql
     */
    public function sql($execSql){
        $this->execSql=$execSql;
        return $this;
    }

    /**
     * 设置字段名称
     */
    public function field($field){
        $this->fields=$field;
        return $this;
    }

    /**
     * 设置注解
     */
    public function comments($comments){
        $this->comments=$comments;
        return $this;
    }

    /**
     * 查询数据库
     * @param $sql
     */
    public function query($sql,$type='',$ttl=0){
        $this->clear();
        $select = strtoupper(substr($sql, 0, 7)) == 'SELECT ' ? 1 : 0;
        if($this->ttl > 0 && $type == 'CACHE' && $select) {
            return $this->_query($sql, $ttl ? $ttl : $this->ttl);
        }
        $query=$this->links[$this->connect]->query($sql);
        if($select=="1"){
            $result = $this->fetch($query);
            if($query){
                $query->free_result();
            }
            return $result;
        }
        if(!$query){
            $error=$this->links[$this->connect]->error;
            throw new \RuntimeException('error->'.$error.',sql->' . $sql);
        }
        return $query;
    }

    /**
     * 检测缓存
     * @param $sql
     * @param $ttl
     * @return array
     */
    public function _query($sql, $ttl) {
        $cid = md5($sql);
        $result = cache()->get($cid);
        if(!$result) {
            $result = $this->query($sql);
            if($result){
                cache()->set($cid, $result, $ttl);
            }
        }
        return $result;
    }

    /**
     * 析构函数-销毁资源
     */
    public function __destruct()
    {
        if($this->links){
            foreach($this->links as $val){
                @$val->close();
            }
            $this->links=array();
        }
    }

    /**
     * 获取数据
     * @param $result
     * @return array
     */
    public function fetch($result)
    {
        $array = array();
        if($result instanceof \mysqli_stmt)
        {
            $variables = array();
            $data = array();
            $meta = $result->result_metadata();
            while($field = $meta->fetch_field()){
                $variables[] = &$data[$field->name];
            }
            call_user_func_array(array($result, 'bind_result'), $variables);
            $i=0;
            while($result->fetch())
            {
                $array[$i] = array();
                foreach($data as $k=>$v)
                    $array[$i][$k] = $v;
                $i++;
            }
        }
        elseif($result instanceof \mysqli_result)
        {
            while($row = $result->fetch_assoc()){
                $array[] = $row;
            }
        }
        return $array;
    }

    /**
     *  解析预处理语句
     */
    public function parsePrepareData()
    {
        $result=array();
        $result["params"]=is_array($this->prepareDataField)?implode("",$this->prepareDataField):$this->prepareDataField;
        $result["value"]=is_array($this->prepareDataValue)?$this->prepareDataValue:array($this->prepareDataValue);
        return $result;
    }

    /**
     * 解析排它锁
     * @return string
     */
    public function parseLock(){
        return $this->lock?"for update":"";
    }

    /**
     * 解析排它锁
     * @return string
     */
    public function parseOrder(){
        $orderStr="";
        foreach($this->order as $key=>$val){
            $orderStr.="`{$key}` {$val},";
        }
        if($orderStr){
            $orderStr="order by ".trim($orderStr,",");
        }
        return $orderStr;
    }

    /**
     * 解析条数
     * @return string
     */
    public function parseLimit(){
        return $this->limit?"limit {$this->limit['offset']},{$this->limit['limit']}":"";
    }

    /**
     * 解析表名
     * @return string
     */
    public function parseTable(){
        return '`'.$this->tableName.'`';
    }

    /**
     * 解析字段名
     * @return string
     */
    public function parseField(){
        return $this->fields;
    }

    /**
     * 解析条件
     * @return string
     */
    public function parseWhere($init=true){
        $whereStr="";
        foreach($this->wheres as $key=>$val){
            $count=count($val);
            if($val[0] instanceof \ltcms\db\Db_raw){
                $field=$val[0]->getContent();
            }else{
                if(strpos($val[0],".")===false){
                    $field="`{$val[0]}`";
                }else{
                    $field=$val[0];
                }
            }
            if($this->prepare){
                if($count ==3){
                    if(is_array($val[2])){
                        $temp=array();
                        foreach($val[2] as $zv){
                            $temp[]="?";
                            if($init){
                                $type=Str::getType($zv,2);
                                if($type!==false){
                                    $this->prepareDataField[]=$type;
                                    $this->prepareDataValue[]=$zv;
                                }
                            }
                        }
                        $value="(".implode(",",$temp).")";
                    }else{
                        $value="?";
                        if($init){
                            $type=Str::getType($val[2],2);
                            if($type!==false){
                                $this->prepareDataField[]=$type;
                                $this->prepareDataValue[]=$val[2];
                            }
                        }
                    }
                    if($val[1]=="nozhan"){
                        $whereStr.=$field ." and ";
                    }else{
                        $whereStr.=$field.' '.$val[1].' '.$value ." and ";
                    }
                }else if($count==2){
                    $value="?";
                    $whereStr.=$field.' = '.$value ." and ";
                    if($init){
                        $type=Str::getType($val[1],2);
                        if($type!==false){
                            $this->prepareDataField[]=$type;
                            $this->prepareDataValue[]=$val[1];
                        }
                    }
                }
            }else{
                if($count ==3){
                    if(is_numeric($val[2])){
                        $value=$val[2];
                    }else if(is_array($val[2])){
                        $temp=array();
                        foreach($val[2] as $zv){
                            $temp[]=is_numeric($zv)?$zv:"'{$zv}'";
                        }
                        $value="(".implode(",",$temp).")";
                    }else{
                        $value="'{$val[2]}'";
                    }
                    $whereStr.=$field.' '.$val[1].' '.$value ." and ";
                }else if($count==2){
                    if(is_numeric($val[1])){
                        $value=$val[1];
                    }else{
                        $value="'{$val[1]}'";
                    }
                    $whereStr.=$field.' = '.$value ." and ";
                }
            }
        }
        if($whereStr){
            $whereStr="where ".trim($whereStr," and");
        }
        return $whereStr;
    }

    /**
     * 解析注释
     * @return string
     */
    public function parseComment(){
        return $this->comments;
    }

    /**
     * 解析连表
     * @return string
     */
    public function parseJoin(){
        return   implode(" ", $this->join);
    }

    /**
     * group
     */
    public function group($key){
        if(is_array($key)){
            $this->group=array_merge($this->group,$key);
        }else{
            $this->group[]=$key;
        }
        return $this;
    }

    /**
     * 解析group
     * @return string
     */
    public function parseGroup(){
        if($this->group){
            return  "group by ". implode(",", $this->group);
        }
        return "";
    }

    /**
     * 解析数据
     * @return string
     */
    public function parseData($data=null,$batch=false){
        $data=is_null($data)?$this->data:$data;
        $dataMap=array();
        if($data){
            if($batch){
                if($this->prepare){
                    $tempData=array();
                    foreach($data[0] as $key=>$val){
                        $dataMap["fields"][]="`{$key}`";
                        $tempData[]="?";
                    }
                    $rowValue="(".implode(",",$tempData).")";
                    foreach($data as $key=>$row){
                        $dataMap["data"][]=$rowValue;
                        foreach($row as $rkey=>$rval){
                            if($rval instanceof \ltcms\db\Db_raw){
                                $this->prepareDataField[]="s";
                                $this->prepareDataValue[]=$rval->getContent();
                            }else{
                                $type=Str::getType($rval,2);
                                if($type!==false){
                                    $this->prepareDataField[]=$type;
                                    $this->prepareDataValue[]=$rval;
                                }
                            }
                        }
                    }
                }else{
                    $tempData=array();
                    foreach($data[0] as $key=>$val){
                        $dataMap["fields"][]="`{$key}`";
                        if($val instanceof \ltcms\db\Db_raw){
                            $tempData[]=$val->getContent();
                        }else{
                            $tempData[]=is_numeric($val)?$val:"'{$val}'";
                        }
                    }
                    $rowValue="(".implode(",",$tempData).")";
                    foreach($data as $key=>$row){
                        foreach($row as $rkey=>$rval){
                            $dataMap["data"][]=$rowValue;
                        }
                    }
                }
            }else{
                if($this->prepare){
                    foreach($data as $key=>$val){
                        $dataMap["fields"][]="`{$key}`";
                        $dataMap["data"][]="?";
                        if($val instanceof \ltcms\db\Db_raw){
                            $this->prepareDataField[]="s";
                            $this->prepareDataValue[]=$val->getContent();
                        }else{
                            $type=Str::getType($val,2);
                            if($type!==false){
                                $this->prepareDataField[]=$type;
                                $this->prepareDataValue[]=$val;
                            }
                        }
                    }
                }else{
                    foreach($data as $key=>$val){
                        $dataMap["fields"][]="`{$key}`";
                        if($val instanceof \ltcms\db\Db_raw){
                            $dataMap["data"][]=$val->getContent();
                        }else{
                            $dataMap["data"][]=is_numeric($val)?$val:"'{$val}'";
                        }
                    }
                }
            }
        }
        if($dataMap){
            $dataMap["fields"]=implode(",",$dataMap["fields"]);
            $dataMap["data"]=$batch?"VALUES ".implode(",",$dataMap["data"]):implode(",",$dataMap["data"]);
        }
        return $dataMap;
    }

    /**
     * 解析set 数据
     * @return string
     */
    public function parseSet($data=null){
        $data=is_null($data)?$this->data:$data;
        $setMap=array();
        if($data){
            if($this->prepare){
                foreach($data as $key=>$val){
                    $key="`{$key}`";
                    $setMap[]=$key.' = ?';
                    if($val instanceof \ltcms\db\Db_raw){
                        $this->prepareDataField[]="s";
                        $this->prepareDataValue[]=$val;
                    }else{
                        $type=Str::getType($val,2);
                        if($type!==false){
                            $this->prepareDataField[]=$type;
                            $this->prepareDataValue[]=$val;
                        }
                    }
                }
            }else{
                foreach($data as $key=>$val){
                    $key="`{$key}`";
                    if($val instanceof \ltcms\db\Db_raw){
                        $val=$val->getContent();
                    }else{
                        $val=is_numeric($val)?$val:"'".$val."'";
                    }
                    $setMap[]=$key.' = '.$val ."";
                }
            }
        }
        return implode(",",$setMap);
    }

    /**
     * 执行sql语句
     * @param $sql
     */
    public function execute($sql){
        $this->connect();
        $query=$this->query($sql);
        return $query;
    }

    /**
     *  查询
     */
    public function select($sql=null,$type=Null,$ttl=Null)
    {
        $this->connect();
        $tableNamne=$this->tableName;
        if(is_null($sql)){
            $sql= trim(str_replace(
                ['%TABLE%','%FIELD%', '%WHERE%',  '%ORDER%',  '%LIMIT%', '%LOCK%', '%COMMENT%',"%JOIN%","%GROUP%"],
                [
                    $this->parseTable(),
                    $this->parseField(),
                    $this->parseWhere(),
                    $this->parseOrder(),
                    $this->parseLimit(),
                    $this->parseLock(),
                    $this->parseComment(),
                    $this->parseJoin(),
                    $this->parseGroup(),
                ],$this->selectSql));
        }else{
            //替换sql参数
            if($this->prepareReplaceField){
                $sql=trim(str_replace($this->prepareReplaceField,"?",$sql));
            }
        }
        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                if($tableNamne){
                    event()->trigger("db.{$tableNamne}.before_select");
                }
                $sqlResult=$this->stmtQuery($sql,$parsePrepareData,1);
                if($tableNamne){
                    event()->trigger("db.{$tableNamne}.after_select");
                }
                return $sqlResult;
            }
        }
        if($tableNamne){
            event()->trigger("db.{$tableNamne}.before_select");
        }
        $sqlResult=$this->query($sql,$type,$ttl);
        if($tableNamne){
            event()->trigger("db.{$tableNamne}.after_select");
        }
        return $sqlResult;
    }

    /**
     *  查询
     */
    public function paginate($listRows = null,$type=Null,$ttl=Null)
    {
        $this->connect();
        if($this->execSql){
            $sql=$this->execSql;
            //替换sql参数
            if($this->prepareReplaceField){
                $sql=trim(str_replace($this->prepareReplaceField,"?",$sql));
            }
            $totalSql= "select count(*) as total from({$sql}) as page";
        }else{
            $totalSql= trim(str_replace(
                ['%TABLE%','%FIELD%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%',"%JOIN%","%GROUP%"],
                [
                    $this->parseTable(),
                    "count(*) as total",
                    $this->parseWhere(),
                    "",
                    "",
                    "",
                    "",
                    $this->parseJoin(),
                    "",
                ],$this->paginateSql));

            $sql= trim(str_replace(
                ['%TABLE%','%FIELD%', '%WHERE%',  '%ORDER%','%LOCK%', '%COMMENT%',"%JOIN%","%GROUP%"],
                [
                    $this->parseTable(),
                    $this->parseField(),
                    $this->parseWhere(false),
                    $this->parseOrder(),
                    $this->parseLock(),
                    $this->parseComment(),
                    $this->parseJoin(),
                    $this->parseGroup(),
                ],$this->paginateSql));
        }

        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["totalSql"]=$totalSql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        $page=new \ltcms\lib\Page();
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                return $page->stmtQuery($totalSql,$sql,$parsePrepareData,$listRows,$this->connect);
            }
        }
        return $page->query($totalSql,$sql,$listRows,$this->connect);
    }

    /**
     *  查询 - 二次查询
     */
    public function paginate2($listRows = null,$type=Null,$ttl=Null)
    {
        $this->connect();
        if($this->execSql){
            $sql=$this->execSql;
            //替换sql参数
            if($this->prepareReplaceField){
                $sql=trim(str_replace($this->prepareReplaceField,"?",$sql));
            }
            $totalSql= "select count(*) as total from({$sql}) as page";
        }else{
            $totalSql= trim(str_replace(
                ['%TABLE%','%FIELD%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%',"%JOIN%","%GROUP%"],
                [
                    $this->parseTable(),
                    "count(*) as total",
                    $this->parseWhere(),
                    "",
                    "",
                    "",
                    "",
                    $this->parseJoin(),
                    "",
                ],$this->paginateSql));
            $this->where($this->wheres_extra);
            $sql= trim(str_replace(
                ['%TABLE%','%FIELD%', '%WHERE%',  '%ORDER%','%LOCK%', '%COMMENT%',"%JOIN%","%GROUP%"],
                [
                    $this->parseTable(),
                    $this->parseField(),
                    $this->parseWhere(false),
                    $this->parseOrder(),
                    $this->parseLock(),
                    $this->parseComment(),
                    $this->parseJoin(),
                    $this->parseGroup(),
                ],$this->paginate2Sql));
        }
        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["totalSql"]=$totalSql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        $page=new \ltcms\lib\Page();
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                return $page->stmtQuery($totalSql,$sql,$parsePrepareData,$listRows,$this->connect);
            }
        }
        return $page->query($totalSql,$sql,$listRows,$this->connect);
    }

    /**
     * 绑定参数查询
     * @param $sql
     */
    public function stmtQuery($sql,$parsePrepareData,$type=null){
        /* 创建预处理语句 */
        $this->clear();
        $stmt = $this->links[$this->connect]->prepare($sql);
        if($stmt){
            /* 为标记绑定参数 */
            $stmt->bind_param($parsePrepareData["params"], ...$parsePrepareData["value"]);
            /* 执行查询 */
            $ok=$stmt->execute();
            if(!$ok){
                $error=$stmt->error;
                $stmt->close();
                throw new \RuntimeException('error->'.$error.',sql->' . $sql.',params->'.toJsonEncode($parsePrepareData));
            }else{
                if($type=="1"){
                    $result=$this->fetch($stmt);
                    $stmt->close();
                    return $result;
                }else if($type=="2"){
                    $affected_rows=$stmt->affected_rows;
                    $stmt->close();
                    return $affected_rows;
                }
            }
        }else{
            $error=$this->links[$this->connect]->error;
            throw new \RuntimeException('error->'.$error.'sql-> ' . $sql);
        }
        throw new \RuntimeException('sql-> ' . $sql);
    }

    /**
     *  查找数据[单条]
     */
    public function find($sql=null,$type=Null,$ttl=Null)
    {
        $this->connect();
        if(is_null($sql)){
            $sql= trim(str_replace(
                ['%TABLE%','%FIELD%', '%ORDER%', '%WHERE%', '%LOCK%', '%COMMENT%',"%JOIN%"],
                [
                    $this->parseTable(),
                    $this->parseField(),
                    $this->parseOrder(),
                    $this->parseWhere(),
                    $this->parseLock(),
                    $this->parseComment(),
                    $this->parseJoin(),
                ],$this->findSql));

        }else{
            //替换sql参数
            if($this->prepareReplaceField){
                $sql=trim(str_replace($this->prepareReplaceField,"?",$sql));
            }
        }
        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                $result=$this->stmtQuery($sql,$parsePrepareData,1);
                return $result?$result[0]:$result;
            }
        }
        $result = $this->query($sql,$type,$ttl);
        return $result?$result[0]:$result;
    }

    /**
     *  添加数据
     */
    public function insert($data = null)
    {
        $this->connect();
        $data=$this->parseData($data);
        $sql= trim(str_replace(
            ['%TABLE%','%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $this->parseTable(),
                $data["fields"],
                $data["data"],
                $this->parseComment(),
            ],$this->insertSql));

        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }

        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                $affected_rows=$this->stmtQuery($sql,$parsePrepareData,2);
                $insert_id=$this->links[$this->connect]->insert_id;
                $result=array();
                $result["insert_id"]=$insert_id;
                $result["affected_rows"]=$affected_rows;
                return $result;
            }
        }

        $this->execute($sql);
        $insert_id=$this->links[$this->connect]->insert_id;
        $affected_rows=$this->links[$this->connect]->affected_rows;
        $result=array();
        $result["insert_id"]=$insert_id;
        $result["affected_rows"]=$affected_rows;
        return $result;
    }

    /**
     *  添加数据[多条]
     */
    public function insertBatch($data = null)
    {
        $this->connect();
        $data=$this->parseData($data,true);
        $sql= trim(str_replace(
            ['%TABLE%','%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $this->parseTable(),
                $data["fields"],
                $data["data"],
                $this->parseComment(),
            ],$this->insertAllSql));
        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                $affected_rows=$this->stmtQuery($sql,$parsePrepareData,2);
                $result=array();
                $result["affected_rows"]=$affected_rows;
                return $result;
            }
        }

        $this->execute($sql);
        $affected_rows=$this->links[$this->connect]->affected_rows;
        $result=array();
        $result["affected_rows"]=$affected_rows;
        return $result;
    }

    /**
     *  更新数据
     */
    public function update($data=null)
    {
        $this->connect();
        $sql= trim(str_replace(
            ['%TABLE%','%SET%', '%WHERE%', '%COMMENT%'],
            [
                $this->parseTable(),
                $this->parseSet($data),
                $this->parseWhere(),
                $this->parseComment(),
            ],$this->updateSql));
        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                $affected_rows=$this->stmtQuery($sql,$parsePrepareData,2);
                $result=array();
                $result["affected_rows"]=$affected_rows;
                return $result;
            }
        }

        $this->execute($sql);
        $affected_rows=$this->links[$this->connect]->affected_rows;
        $result=array();
        $result["affected_rows"]=$affected_rows;
        return $result;
    }

    /**
     *  删除数据
     */
    public function delete()
    {
        $this->connect();
        $sql= trim(str_replace(
            ['%TABLE%', '%WHERE%', '%COMMENT%'],
            [
                $this->parseTable(),
                $this->parseWhere(),
                $this->parseComment(),
            ],$this->deleteSql));
        if($this->fetchSql){
            $parsePrepareData=array();
            if($this->prepare) {
                $parsePrepareData = $this->parsePrepareData();
            }
            $result=array();
            $result["sql"]=$sql;
            $result["prepareData"]=$parsePrepareData;
            return $result;
        }
        if($this->prepare){
            $parsePrepareData=$this->parsePrepareData();
            if($parsePrepareData["params"]){
                $affected_rows=$this->stmtQuery($sql,$parsePrepareData,2);
                $result=array();
                $result["affected_rows"]=$affected_rows;
                return $result;
            }
        }

        $this->execute($sql);
        $affected_rows=$this->links[$this->connect]->affected_rows;
        $result=array();
        $result["affected_rows"]=$affected_rows;
        return $result;
    }
}

